<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of MSSQL_Database
 *
 * @author ervinne
 */
class Database  extends Config{

    protected $server;
    protected $username;
    protected $password;
    protected $default_database;

    public function __construct($server=null, $database=null, $user=null, $password=null) {
    
        $this->server = DB_HOSTNAME;
        $this->username = DB_USERNAME;
        $this->password = DB_PASSWORD;

        if ($database) {
            $this->default_database = DB_NAME;
        }
    }

    public function connect() {
        $options = array(
            'UID' => $this->username,
            'PWD' => $this->password,
            'ReturnDatesAsStrings'=> true
        );

        $connection = sqlsrv_connect($this->server, $options);

        //  validate connection and throw an exception for any errors
        if (!$connection) {
            throw new Exception("Unable to establish connection: " . $this->get_last_error());
        }

        return ($connection ? $connection : null);
    }

    public function tbl_name($tbl_name){
        return "[" . DB_NAME . "]" . ".[dbo]."  ."[" . $tbl_name . "]";
    }
    
    public function escape($str) {
        return "'" . addslashes($str) . "'";
    }

    public function query($query, $fetch_type) {
        $connection = $this->connect();
        $params = array();
        $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
        $stmt = sqlsrv_query($connection, $query, $params, $options);
        $response = array();

        if (!$stmt) {
            throw new Exception("Error creating sql statement: " . $this->get_last_error());
        }

        if ($fetch_type === "array") {
            while ($row = sqlsrv_fetch_array($stmt)) {
                $response[] = $row;
            }
        } else if ($fetch_type === "row") {
            $response = sqlsrv_fetch_array($stmt);
        } else if ($fetch_type === "update") {
            $response = sqlsrv_rows_affected($stmt);
        } else if ($fetch_type === "insert") {
            sqlsrv_next_result($stmt);
            sqlsrv_fetch($stmt);
            $response = sqlsrv_get_field($stmt, 0);//   for some reason this always returns false
        } else if ($fetch_type === "numrows") {
            $response = sqlsrv_num_rows($stmt);
        }

        //  disconnect
        sqlsrv_free_stmt($stmt);
        sqlsrv_close($connection);
        
        return $response;
    }

    public function update($table, $fields = array(), $conditions = array()) {
        $field_string = "";
        $cond_string = "";
        foreach ($fields as $field => $value) {
            $value = is_numeric($value) ? $value : $this->escape($value);
            $field_string .= $field . "=" . $value . ",";
        }
        foreach ($conditions as $condition => $value) {
            $value = is_numeric($value) ? $value : $this->escape($value);
            $cond_string .= $condition . "=" . $value . " AND ";
        }
        $field_string = substr($field_string, 0, strlen($field_string) - 1);
        $cond_string = substr($cond_string, 0, strlen($cond_string) - 4);
        $query = $this->query("UPDATE {$table} SET {$field_string} WHERE {$cond_string}", "update");
        return $query;
//        return "UPDATE {$table} SET {$field_string} WHERE {$cond_string}";
    }

    public function insert($table, $fields = array()) {
        $field_string = "";
        $value_string = "";
        foreach ($fields as $field => $value) {
            $value = is_numeric($value) ? $value : $this->escape($value);
            $field_string .= "[".$field . "],";
            $value_string .= $value . ",";
        }
        $field_string = substr($field_string, 0, strlen($field_string) - 1);
        $value_string = substr($value_string, 0, strlen($value_string) - 1);
        $query = $this->query("INSERT INTO {$table} ( {$field_string} ) VALUES ( {$value_string} )  ; SELECT SCOPE_IDENTITY() AS IDENTITY_COLUMN_NAME", "insert");
        return $query;        
        //return "INSERT INTO {$table} ( {$field_string} ) VALUES ( {$value_string} )";
    }

    public function delete($table,$conditions=array())
    {
        $cond_string="";
        foreach($conditions as $condition => $value)
        {
            $value = is_numeric($value) ? $value : $this->escape($value); 
            $cond_string .= $condition . "=" . $value .  " AND ";
        }
        $cond_string = substr($cond_string,0,strlen($cond_string) -4 );
        $query = $this->query("DELETE FROM {$table} WHERE {$cond_string}","update");
        return $query;
    }
    
    
    public function get_last_error() {
        return print_r(sqlsrv_errors(), TRUE);
    }

}